package com.fuelwallet.model;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class HistoryHandle {
	private SQLiteDatabase db;
	private HistorySQLiteHelper dbHelper;
	
	public HistoryHandle(Context context){
		dbHelper = new HistorySQLiteHelper(context);
	}
	
	public void open() throws SQLException {
		db = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
	}
	
	public void putHistory(final String account_name, final float distance, final float cost ){
		
		String args[] = { };
		
		//Cursor result = db.rawQuery("SELECT datetime(\'now\',\'localtime\')", args);
		Cursor result = db.rawQuery("SELECT date(\'now\',\'localtime\')", args);
		result.moveToFirst();
		String date = result.getString(0);
		result.close();
		
		db.execSQL("INSERT INTO " + HistorySQLiteHelper.TABLE_HISTORY + " (" 
			+ HistorySQLiteHelper.COLUMN_ACCOUNTNAME + ","
			+ HistorySQLiteHelper.COLUMN_DATE + ","
			+ HistorySQLiteHelper.COLUMN_DISTANCE + ","
			+ HistorySQLiteHelper.COLUMN_COST + ")"
			+ " VALUES ("
			+ "\"" + account_name + "\","
			+ "\"" + date + "\","
			+ distance + ","
			+ cost +")" ); 
		
	}
	
	public void deleteHistory(){
		//db.execSQL("TRUNCATE TABLE " + HistorySQLiteHelper.TABLE_HISTORY);
		db.execSQL("DELETE FROM " + HistorySQLiteHelper.TABLE_HISTORY);
		db.execSQL("VACUUM");
	}
	
	public void deleteHistory(final String account_name){
		db.execSQL("DELETE FROM " + HistorySQLiteHelper.TABLE_HISTORY
				+ " WHERE " + HistorySQLiteHelper.COLUMN_ACCOUNTNAME + " = \"" + account_name+"\"");
	}
	
	/*public FuelPrice getFuelPrice(final String fuel_name){
		FuelPrice out_fuelPrice;
		float fuel_price;
		
		String args[] = { };
		
		Cursor result = db.rawQuery("SELECT " + HistorySQLiteHelper.COLUMN_FUELPRICE
				+ " FROM "+HistorySQLiteHelper.TABLE_HISTORY 
				+ " WHERE " + HistorySQLiteHelper.COLUMN_FUELNAME + " = \""+fuel_name+"\"", args);
		
		result.moveToFirst();
		fuel_price = result.getFloat(0);
		
		out_fuelPrice=new FuelPrice(fuel_name, fuel_price);	
		
		result.close();
		return out_fuelPrice;
	}*/
	public List<History> getHistory(final String account_name){
		List<History> out_history = new ArrayList<History>();
		String args[] = { };
		
		Cursor result = db.rawQuery("SELECT " + HistorySQLiteHelper.COLUMN_DATE + ","
				//+ HistorySQLiteHelper.COLUMN_FROM + ","
				//+ HistorySQLiteHelper.COLUMN_TO + ","
				+ HistorySQLiteHelper.COLUMN_DISTANCE + ","
				+ HistorySQLiteHelper.COLUMN_COST
				+ " FROM "+HistorySQLiteHelper.TABLE_HISTORY 
				+ " WHERE " + HistorySQLiteHelper.COLUMN_ACCOUNTNAME + " = \""+account_name+"\"", args);
		
		result.moveToFirst();
		while(!result.isAfterLast()){
			String date = result.getString(0);
			//from = result.getString(1);
			//to = result.getString(2);
			float distance = result.getFloat(1);
			float cost = result.getFloat(2);
			out_history.add(new History(account_name,date,distance,cost));
			result.moveToNext();
		}
		
		result.close();
		
		return out_history;
	}
	
	public List<String> getAccountNameList(){
		List<String> name_list = new ArrayList<String>();
		String[] args = { };
		
		Cursor result = db.rawQuery("SELECT DISTINCT " + HistorySQLiteHelper.COLUMN_ACCOUNTNAME 
				+ " FROM " + HistorySQLiteHelper.TABLE_HISTORY, args);
		
		result.moveToFirst();
		while (!result.isAfterLast()) {
			name_list.add(result.getString(0));
			result.moveToNext();
		}
		
		result.close();
		return name_list;
	}
	
	public float getDistanceSum(String account_name){
		String[] args = { };
		
		Cursor result = db.rawQuery("SELECT total("
				+ HistorySQLiteHelper.COLUMN_DISTANCE + ")"
				+ " FROM "+HistorySQLiteHelper.TABLE_HISTORY 
				+ " WHERE " + HistorySQLiteHelper.COLUMN_ACCOUNTNAME + " = \""+account_name+"\"", args);
		
		result.moveToFirst();
		float total_distance = result.getFloat(0);
		result.close();
		
		return total_distance;	
	}
	
	public float getCostSum(String account_name){
		String[] args = { };
		
		Cursor result = db.rawQuery("SELECT total("
				+ HistorySQLiteHelper.COLUMN_COST + ")"
				+ " FROM "+HistorySQLiteHelper.TABLE_HISTORY 
				+ " WHERE " + HistorySQLiteHelper.COLUMN_ACCOUNTNAME + " = \""+account_name+"\"", args);
		
		result.moveToFirst();
		float total_cost = result.getFloat(0);
		result.close();
		
		return total_cost;	
	}
	
	/*public List<String> getFuelNameList(){
		List<String> NameList = new ArrayList<String>();
		String[] args = { };
		
		Cursor result = db.rawQuery("SELECT " + HistorySQLiteHelper.COLUMN_FUELNAME 
				+ " FROM " + HistorySQLiteHelper.TABLE_HISTORY, args);
		
		result.moveToFirst();
		while (!result.isAfterLast()) {
			NameList.add(result.getString(0));
			result.moveToNext();
		}
		
		result.close();
		return NameList;
	}*/
	
	public class HistorySQLiteHelper extends SQLiteOpenHelper {

		public static final String TABLE_HISTORY = "history";
		public static final String COLUMN_ID = "_id";
		//public static final String COLUMN_FUELNAME = "fuel_name";
		//public static final String COLUMN_FUELPRICE = "fuel_price";
		public static final String COLUMN_ACCOUNTNAME = "account_name";
		public static final String COLUMN_DATE = "date";
		public static final String COLUMN_DISTANCE = "distance";
		public static final String COLUMN_COST = "cost";
		

		private static final String DATABASE_NAME = "history.db";
		private static final int DATABASE_VERSION = 1;

		// Database creation SQL statement
		private static final String DATABASE_CREATE = "create table "
				+ TABLE_HISTORY + "( " 
				+ COLUMN_ID + " integer primary key autoincrement, " 
				+ COLUMN_ACCOUNTNAME + " text not null, "
				+ COLUMN_DATE + " text not null, "
				+ COLUMN_DISTANCE + " real not null, "
				+ COLUMN_COST + " real not null);";

		public HistorySQLiteHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase database) {
			database.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(HistorySQLiteHelper.class.getName(),
					"Upgrading database from version " + oldVersion + " to "
							+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS" + TABLE_HISTORY);
			onCreate(db);
		}
	}
	

}